package com.kfm.jdbc;

import com.mysql.cj.jdbc.Driver;

import java.sql.*;

public class Demo {

    public static void main(String[] args) {
        // getGenerateKey();
        selectAll2();

    }

    /**
     * 插入数据
     */
    public static void insert01() {
        Connection connection = null;
        Statement statement = null;
        try {
            // 第一种方法
            //DriverManager.registerDriver(new Driver());
            // 第二种[推荐] 1. 加载驱动 实现了解耦 降低耦合度
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println(Driver.class.getName());
            // Class.forName(Driver.class.getName());
            String url = "jdbc:mysql://localhost:3306/store";
            String user = "root";
            String password = "";
            // 2. 获取数据库连接
            connection = DriverManager.getConnection(url, user,password);
            // 3. 拼写sql
            String sql = "insert into kfm values(5, 'john',2)";
            // 4. 获取发送sql 执行sql的对象
            statement = connection.createStatement();
            // 5. 执行sql并返回结果 insert/update/delete --> executeUpdate(String sql)  select --> executeQuery(String sql)
            int rows = statement.executeUpdate(sql);
            System.out.println("共有" + rows + "行数据被执行");

        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    public static void getGenerateKey() {

        // 1. 注册驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        // try-with-resource(IO)
        String sql = "insert into users(name, password) values('张三', '123456'),('李四', '123456')";
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/store", "root", "");
             Statement statement = connection.createStatement();) {
            int i = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
            System.out.println("共有" + i + "行数据被执行...");
            ResultSet keys = statement.getGeneratedKeys();
            while (keys.next()) {
                System.out.println("生成的自增主键" + keys.getInt("id"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        //try {

            // 2. 获取数据库连接
            //DriverManager.getConnection("jdbc:mysql://localhost:3306/store?user=root&password=");
            //Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/store", "root", "");
            // 3.拼写SQL
            //String sql = "insert into users(name, password) values('张三', '123456'),('李四', '123456')";
            // 4.获取执行sql的对象
            //Statement statement = connection.createStatement();
            // 5.执行sql RETURN_GENERATED_KEYS 代表获取自增主键
            //int i = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
            //System.out.println("共有" + i + "行数据被执行...");
            //ResultSet keys = statement.getGeneratedKeys();
            //while (keys.next()) {
            //    System.out.println("生成的自增主键" + keys.getInt("id"));
            //}
        //} catch (ClassNotFoundException e) {
        //    throw new RuntimeException(e);
        //} catch (SQLException e) {
        //    throw new RuntimeException(e);
        //}
    }

    /**
     * 查询所有
     */
    public static void selectAll() {
        // 1.加载驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2.建立连接
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/store?user=root&password=");
            // 3. 获取执行sql的对象
            Statement statement = connection.createStatement();
            String sql = "select * from users";
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                System.out.print("id: " + resultSet.getObject("id") + "\t");
                System.out.print("name: " + resultSet.getObject("name") + "\t");
                System.out.print("password: " + resultSet.getObject(3) + "\t");
                System.out.println();
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取列信息
     */
    public static void selectAll2() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/store?user=root&password=");
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select * from users");
            ResultSetMetaData metaData = resultSet.getMetaData();
            int count = metaData.getColumnCount();
            for (int i = 0; i < count; i++) {
                System.out.print(metaData.getColumnLabel(i + 1) + ":" + metaData.getColumnTypeName(i + 1) + metaData.getColumnClassName(i + 1) + "\t");
            }
            System.out.println();
            while (resultSet.next()) {
                for (int i = 0; i < count; i++) {
                    String colName = metaData.getColumnLabel(i + 1);
                    //System.out.print(metaData.getColumnLabel(i + 1) + "\t");
                    Object colValue = resultSet.getObject(colName);
                    System.out.print(colValue + "\t");
                }
                    System.out.println();
                //String col1 = metaData.getColumnName(1);
                //System.out.print(resultSet.getInt(col1) + "\t");
                //String col2 = metaData.getColumnLabel(2);
                //System.out.print(resultSet.getString(col2) + "\t");
                //String col3 = metaData.getColumnName(3);
                //System.out.print(resultSet.getString(col3) + "\t");
                //System.out.println();
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }

            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }

            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}
